package com.ais.gaea;

import com.ais.gaea.annotation.Table;
import com.ais.gaea.annotation.TableColumn;
import com.ais.gaea.annotation.TableConfiguration;
import com.ais.gaea.constant.Constants;
import com.ais.gaea.dialect.DBInstance;
import com.ais.gaea.dialect.DMInstance;
import com.ais.gaea.dialect.MySQLInstance;
import lombok.Setter;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeansException;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.ObjectUtils;
import org.springframework.util.StringUtils;

import java.sql.SQLException;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

/**
 * @author xuning
 * @date 2022/11/26
 **/
@Slf4j
@ConfigurationProperties(prefix = Constants.PROPERTIES_PREFIX)
public class GaeaTemplate implements ApplicationContextAware, InitializingBean {

    private ApplicationContext applicationContext;

    private final JdbcTemplate jdbcTemplate;

    private DBInstance dbInstance;

    @Setter
    private String dbType;

    @Setter
    private String schemaName;

    @Setter
    private String tablePackageName;

    public GaeaTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }


    @Transactional(rollbackFor = Exception.class)
    public void initDatabase() throws SQLException {
        Set<Class<?>> tableClass = AnnotationUtils.scan(tablePackageName, Table.class);
        // 1. 创建表schema
        initTableSchema(tableClass);
        // 2. 初始化表数据
        createTableData(tableClass, true);
    }

    /**
     * 初始化或升级数据库表中的数据
     *
     * @param tableSet 数据库表集合
     * @param init     init/true upgrade/false
     * @throws SQLException SQLException
     */

    private void createTableData(Set<Class<?>> tableSet, boolean init) throws SQLException {
        String tableName = "";
        try {
            for (Class<?> table : tableSet) {
                tableName = AnnotationUtils.getTableName(table);
                TableConfiguration tableConfiguration = AnnotationUtils.getTableConfiguration(table);
                if (tableConfiguration != null) {
                    Class<?>[] operations = tableConfiguration.operation();
                    for (Class<?> operation : operations) {
                        TableOperation to = applicationContext.getBean(StringUtils.uncapitalize(operation.getSimpleName()),
                                TableOperation.class);
                        if (init) {
                            to.initialize();
                        } else {
                            to.upgrade();
                        }
                    }
                }
            }
        } catch (SQLException ex) {
            log.error("initialize {} table data failed", tableName, ex);
            throw ex;
        }
    }

    private void initTableSchema(Set<Class<?>> tableSet) {
        String tableName = null;
        List<String> sqlList = new ArrayList<>();
        String sql = "";
        try {
            for (Class<?> table : tableSet) {
                tableName = AnnotationUtils.getTableName(table);
                //如果表结构存在，先进行删除,以免历史表造成未知影响
                sqlList.add(dbInstance.dropTableIfExistStatement(table));
                //创建表结构
                sqlList.addAll(dbInstance.createTableStatement(table));
                Iterator<String> iterator = sqlList.iterator();
                while(iterator.hasNext()) {
                    sql = iterator.next();
                    jdbcTemplate.execute(sql);
                }
                sqlList.clear();
            }
        } catch (DataAccessException ex) {
            log.error("create {} table failed, sql {}", tableName, sql, ex);
            throw ex;
        }
    }


    /**
     * 升级的流程
     *   1. 先获取新增加的table，然后初始化这些新的table
     *   2. 对已经存在的table，进行检查是否有字段增删，以及类型变化等
     *   3. 对已经删除的table，不进行任何操作。
     * @throws SQLException  throw sqlException
     */
    @Transactional(rollbackFor = Exception.class)
    public void upgradeDatabase() throws SQLException {
        Set<Class<?>> tableClazz = AnnotationUtils.scan(tablePackageName, Table.class);
        //获取新增加的表列表
        Set<Class<?>> newTableClazz = obtainNewTables(tableClazz);

        //新增加的表进行创建和数据初始化
        initTableSchema(newTableClazz);
        createTableData(newTableClazz, true);

        //获取需要升级的表
        HashSet<Class<?>> upgradeTables = new HashSet<>(tableClazz);
        upgradeTables.removeAll(newTableClazz);

        upgradeTableSchema(upgradeTables);
        createTableData(upgradeTables, false);
    }

    private void upgradeTableSchema(HashSet<Class<?>> tableSet) {
        String tableName = "";
        try {
            Iterator<Class<?>> iterator = tableSet.iterator();
            while(iterator.hasNext()) {
                Class<?> table = iterator.next();
                tableName = AnnotationUtils.getTableName(table);
                ColumnAttribute[] columnAttributes = obtainColumnAttribute(tableName);
                tableName = AnnotationUtils.getTableName(table);
                TableColumn[] tableColumns = AnnotationUtils.getTableColumns(table);

                Map<String, List<TableColumn>> divideTableColumnMap = divideTableColumn(columnAttributes, tableColumns, tableName);

                List<TableColumn> updateColumnList = divideTableColumnMap.get(Constants.UPDATE_COLUMN_KEY);
                List<TableColumn> addColumnList = divideTableColumnMap.get(Constants.ADD_COLUMN_KEY);

                // 1. 更新字段属性
                updateColumns(updateColumnList, tableName);
                // 2. 增加字段
                addColumns(addColumnList, tableName);
                //既没有变化字段属性，也没有增加字段，就不需要执行后边的Operation了
                if (ObjectUtils.isEmpty(updateColumnList) && ObjectUtils.isEmpty(addColumnList)) {
                    iterator.remove();
                }
            }
        } catch (DataAccessException ex) {
            log.error("upgrade {} table schema failed", tableName, ex);
            throw ex;
        }
    }

    private Map<String, List<TableColumn>> divideTableColumn(ColumnAttribute[] columnAttributes, TableColumn[] tableColumns, String tableName) {
        HashMap<String, List<TableColumn>> key2TableColumnMap = new HashMap<>(20);

        Map<String, TableColumn> map = Arrays.stream(tableColumns)
                .collect(Collectors.toMap(tableColumn -> tableColumn.name().toLowerCase(), Function.identity()));

        //遍历该表的所有字段,将字段进行分组
        for (ColumnAttribute columnAttribute : columnAttributes) {
            TableColumn tableColumn = map.remove(columnAttribute.getName().toLowerCase());
            if (ObjectUtils.isEmpty(tableColumn)) {
                log.info("{} table {} column has been remove in this version, but we stay the same", tableName, columnAttribute.getName());
                continue;
            }
            if (diffTableColumnAndColumnAttribute(tableColumn, columnAttribute)) {
                key2TableColumnMap
                        .computeIfAbsent(Constants.UPDATE_COLUMN_KEY, value -> new ArrayList<>())
                        .add(tableColumn);
            }
        }
        // map中剩余的列就是新增的
        key2TableColumnMap
                .computeIfAbsent(Constants.ADD_COLUMN_KEY,value -> new ArrayList<>())
                .addAll(map.values());

        if (log.isDebugEnabled()) {
            log.debug("updateColumnKey:{}, addColumnKey:{}",
                    key2TableColumnMap.get(Constants.UPDATE_COLUMN_KEY),
                    key2TableColumnMap.get(Constants.ADD_COLUMN_KEY));
        }

        return key2TableColumnMap;
    }


    private ColumnAttribute[] obtainColumnAttribute(String tableName) {
        String sql = dbInstance.getColumnAttributeSql(schemaName, tableName);
        List<ColumnAttribute> attributesList = jdbcTemplate.query(sql, (rs, rowNumber) -> {
            // 将结果转成对应的属性格式
            String field = rs.getString(dbInstance.getField());
            String type = rs.getString(dbInstance.getType());
            String length = rs.getString(dbInstance.getLength());
            String precision = rs.getString(dbInstance.getPrecision());
            String scale = rs.getString(dbInstance.getScale());
            String nullable = rs.getString(dbInstance.getNullable());
            String defaultValue = rs.getString(dbInstance.getDefaultValue());

            return new ColumnAttribute()
                    .setName(field)
                    .setNullable(dbInstance.convertNullable(nullable))
                    .setDefaultValue(dbInstance.convertDefaultValue(defaultValue, type))
                    .setType(dbInstance.convertType(type))
                    .setLength(dbInstance.convertLength(length))
                    .setPrecision(dbInstance.convertPrecision(precision))
                    .setScale(dbInstance.convertScale(scale));
        });

        return attributesList.toArray(new ColumnAttribute[0]);
    }


    private void addColumns(List<TableColumn> addColumnList, String tableName) {
        // computerIfAbsent有可能没有执行，所以获取的list是null，这里需要判断一下
        if (ObjectUtils.isEmpty(addColumnList)) {
            log.debug("add table list is empty");
            return;
        }

        String sql = "";
        String columnName = "";
        try {
            for (TableColumn tableColumn : addColumnList) {
                columnName = tableColumn.name();
                sql = dbInstance.addTableColumnStatement(tableName, tableColumn);
                jdbcTemplate.execute(sql);
            }
        } catch (DataAccessException ex) {
            log.error("add {} table {} column failed, sql:{}", tableName, columnName, sql, ex);
        }
    }

    private void updateColumns(List<TableColumn> updateColumnList, String tableName) {
        // computerIfAbsent有可能没有执行，所以获取的list是null，这里需要判断一下
        if (ObjectUtils.isEmpty(updateColumnList)) {
            log.debug("update table list is empty");
            return;
        }

        String sql = "";
        String columnName = "";
        try {
            for (TableColumn tableColumn : updateColumnList) {
                columnName = tableColumn.name();
                sql = dbInstance.updateTableColumnStatement(tableName, tableColumn);
                jdbcTemplate.execute(sql);
            }
        } catch (DataAccessException ex) {
            log.error("update {} table {} column failed, sql:{}", tableName, columnName, sql, ex);
            throw ex;
        }
    }

    /** 比较数据库中列属性和代码中的属性是否发生变化,比较的顺序
     *   1. 类型是否发生变化
     *   2. 长度是否发生变化(仅检查VARCHAR类型)
     *   3. 精度是否发生变化(仅检查DECIMAL类型)
     *   4. 标度是否发生变化(仅检查DECIMAL,TIME, DATETIME类型)
     *   5. nullable是否发生变化(排序PRIMARY)
     *   6. 默认值是否发生变化(仅设置了defaultValue)
     */

    private boolean diffTableColumnAndColumnAttribute(TableColumn tableColumn, ColumnAttribute columnAttribute) {
        // 映射类型关系
        String type = dbInstance.mappingColumnType(tableColumn.type());
        if (!type.equalsIgnoreCase(columnAttribute.getType())) {
            return true;
        }

        if (needCheckLength(tableColumn.type()) && !String.valueOf(tableColumn.length()).equalsIgnoreCase(columnAttribute.getLength())) {
            return true;
        }

        if (needCheckPrecision(tableColumn.type()) && !String.valueOf(tableColumn.precision()).equalsIgnoreCase(columnAttribute.getPrecision())) {
            return true;
        }

        if (needCheckScale(tableColumn.type()) && !String.valueOf(tableColumn.scale()).equalsIgnoreCase(columnAttribute.getScale())) {
            return true;
        }

        if (needCheckNullable(tableColumn) && tableColumn.nullable() != columnAttribute.getNullable()) {
            return true;
        }

        return (needCheckDefaultValue(tableColumn) && dbInstance.compareDefaultValue(tableColumn, columnAttribute.getDefaultValue()));

    }


    private boolean needCheckDefaultValue(TableColumn tableColumn) {
        return !tableColumn.nullable();
    }

    private boolean needCheckNullable(TableColumn tableColumn) {
        return !tableColumn.primaryKey();
    }

    private boolean needCheckScale(ColumnType type) {
        return type == ColumnType.TIME || type == ColumnType.DECIMAL || type == ColumnType.DATETIME;
    }

    private boolean needCheckPrecision(ColumnType type) {
        return type == ColumnType.DECIMAL;
    }

    private boolean needCheckLength(ColumnType type) {
        return type == ColumnType.VARCHAR;
    }

    private Set<Class<?>> obtainNewTables(Set<Class<?>> tableSet) {
        try {
            HashSet<Class<?>> newTables = new HashSet<>();
            //获取当前schema下所有的表
            String existTableSql = dbInstance.getExistTableSql(schemaName);
            //数据库中已经存在的表，并将所有表名转成小写
            List<String> existTableList = jdbcTemplate.queryForList(existTableSql, String.class)
                    .stream()
                    .map(String::toLowerCase)
                    .collect(Collectors.toList());

            for (Class<?> table : tableSet) {
                if (!existTableList.contains(AnnotationUtils.getTableName(table).toLowerCase())) {
                    newTables.add(table);
                }
            }
            return newTables;
        } catch (DataAccessException ex) {
            log.error("query exist tables failed", ex);
            throw ex;
        }
    }

    public List<String> getEnableMultiTenantTables() {
        List<String> enableMultiTenantTables = new ArrayList<>();
        Set<Class<?>> tableClass = AnnotationUtils.scan(tablePackageName, Table.class);
        for (Class<?> table : tableClass) {
            if (AnnotationUtils.getTableMultiTenantValue(table).equalsIgnoreCase(Constants.TRUE)) {
                enableMultiTenantTables.add(AnnotationUtils.getTableName(table));
            }
        }
        return enableMultiTenantTables;
    }

    @Override
    public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
        this.applicationContext = applicationContext;
    }

    private DBInstance deduceDBInstanceByType() {
        if (Constants.DB_TYPE_MYSQL.equalsIgnoreCase(dbType)) {
            return new MySQLInstance();
        } else if (Constants.DB_TYPE_DM.equalsIgnoreCase(dbType)) {
            return new DMInstance();
        } else {
            throw new IllegalArgumentException("not support " + dbType + " database");
        }
    }
    @Override
    public void afterPropertiesSet() {
        this.dbInstance = deduceDBInstanceByType();
    }
}
